'use strict';

const { Service } = require('ee-core');
const Storage = require('ee-core/storage');
const { console } = require('inspector');
const _ = require('lodash');
const Log = require('ee-core/log');
const dayjs = require('dayjs');
const EE = require('ee-core/ee');
const path = require('path');
const fs = require('fs');
const AdmZip = require('adm-zip');
const FormData = require('form-data');
const axios = require('axios');

/**
 * sqlite数据存储
 * @class
 */
class SqlitedbService extends Service {

  constructor(ctx) {
    super(ctx);

    this.sqliteFile = 'C:\\labs_great_luck\\data\\labs_great_luck.db';
    let sqliteOptions = {
      driver: 'sqlite',
      default: {
        timeout: 6000,
        verbose: console.log // 打印sql语法
      }
    }
    this.demoSqliteDB = Storage.connection(this.sqliteFile, sqliteOptions);
  }

  /*
   * 检查并创建表 (sqlite)
   */
  async checkAndCreateTableSqlite(tableName = '') {
    if (_.isEmpty(tableName)) {
      throw new Error(`table name is required`);
    }
    // 检查表是否存在
    const userTable = this.demoSqliteDB.db.prepare('SELECT * FROM sqlite_master WHERE type=? AND name = ?');
    const result = userTable.get('table', tableName);
    //console.log('result:', result);
    if (result) {
      return;
    }

    // 创建表
    const create_table_user =
      `CREATE TABLE ${tableName}
     (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name CHAR(50) NOT NULL,
        age INT
     );`
    this.demoSqliteDB.db.exec(create_table_user);

  }

  /*
   * 增 Test data (sqlite)
   */
  async addTestDataSqlite(data) {
    //console.log("add data:", data);

    let table = 'user';
    await this.checkAndCreateTableSqlite(table);

    const insert = this.demoSqliteDB.db.prepare(`INSERT INTO ${table} (name, age) VALUES (@name, @age)`);
    insert.run(data);

    return true;
  }

  /*
   * 删 Test data (sqlite)
   */
  async delTestDataSqlite(name = '') {
    //console.log("delete name:", name);

    let table = 'user';
    await this.checkAndCreateTableSqlite(table);

    const delUser = this.demoSqliteDB.db.prepare(`DELETE FROM ${table} WHERE name = ?`);
    delUser.run(name);

    return true;
  }

  /*
   * 改 Test data (sqlite)
   */
  async updateTestDataSqlite(name = '', age = 0) {
    //console.log("update :", {name, age});

    let table = 'user';
    await this.checkAndCreateTableSqlite(table);

    const updateUser = this.demoSqliteDB.db.prepare(`UPDATE ${table} SET age = ? WHERE name = ?`);
    updateUser.run(age, name);

    return true;
  }

  /*
   * 查 Test data (sqlite)
   */
  async getTestDataSqlite(age = 0) {
    //console.log("select :", {age});

    let table = 'user';
    await this.checkAndCreateTableSqlite(table);

    const selectUser = this.demoSqliteDB.db.prepare(`SELECT * FROM ${table} WHERE age = @age`);
    const users = selectUser.all({ age: age });
    //console.log("select users:", users);
    return users;
  }

  /*
   * all Test data (sqlite)
   */
  async getAllTestDataSqlite() {
    //console.log("select all user");

    let table = 'user';
    await this.checkAndCreateTableSqlite(table);

    const selectAllUser = this.demoSqliteDB.db.prepare(`SELECT * FROM ${table} `);
    const allUser = selectAllUser.all();
    //console.log("select allUser:", allUser);
    return allUser;
  }

  /*
   * get data dir (sqlite)
   */
  async getDataDir() {
    const dir = this.demoSqliteDB.getStorageDir();

    return dir;
  }

  /*
   * set custom data dir (sqlite)
   */
  async setCustomDataDir(dir) {
    if (_.isEmpty(dir)) {
      return;
    }

    // the absolute path of the db file
    const dbFile = path.join(dir, this.sqliteFile);
    const sqliteOptions = {
      driver: 'sqlite',
      default: {
        timeout: 6000,
        verbose: console.log
      }
    }
    this.demoSqliteDB = Storage.connection(dbFile, sqliteOptions);

    return;
  }


  /*
   * 执行 sql 脚本
   */
  execDbScript(dbscript) {

    console.log('execDbScript:', dbscript);
    // const insert = this.demoSqliteDB.db.prepare(dbscript);
    // insert.run('');
    this.demoSqliteDB.db.exec(dbscript);

    return true;
  }


  /*
   * 查 CkUser data (sqlite)
   */
  async getCkUserSqlite(data) {
    //console.log("select :", {age});
    const selectUser = this.demoSqliteDB.db.prepare(`SELECT * FROM labs_ck_user WHERE user_name = @userName AND password = @password`);
    const user = selectUser.get(data);
    console.log("select user:", user);
    return user;
  }

  async getWoodStockList(data) {
    //console.log("select :", {age});
    const sql = `SELECT * FROM lk_wood_stock WHERE WOOD_TYPE='wood' AND GOODS_STATUS=@goodsStatus ${data.searchText ? 'AND WOOD_NO LIKE @searchText' : ''} ORDER BY ID DESC LIMIT @pageSize OFFSET @offset`;
    Log.info(`select woodstock list sql: ${sql}`);
    const selectList = this.demoSqliteDB.db.prepare(sql);
    const list = selectList.all(data);

    const countPrepare = this.demoSqliteDB.db.prepare(`SELECT count(id) as total FROM lk_wood_stock WHERE WOOD_TYPE='wood' AND GOODS_STATUS=@goodsStatus ${data.searchText ? 'AND WOOD_NO LIKE @searchText' : ''}`);
    const total = countPrepare.get(data);
    console.log(`select woodstock list: ${list.length}, count: ${total.total}`);
    return { list: list, total: total.total };
  }

  async saveWoodStock(data) {
    const keys = Object.keys(data);
    // const values = Object.values(data);

    // if (data.id) {
    //   // 更新操作
    //   const fieldsToUpdate = keys
    //     .filter(key => key !== 'id') // 除去 id 字段
    //     .map(key => `${key} = @${key}`) // 将字段映射为 key = @key 格式
    //     .join(', '); // 用逗号连接多个字段

    //   const sql = `UPDATE lk_wood_stock SET ${fieldsToUpdate} WHERE id = @id`;

    //   const exec = this.demoSqliteDB.db.prepare(sql);
    //   exec.run(data);
    // } else {
    // 查询原木编号WOOD_NO，是否已经存在，若已存在则返回错误
    const selectWoodNo = this.demoSqliteDB.db.prepare(`SELECT * FROM lk_wood_stock WHERE WOOD_NO=@WOOD_NO`);
    const woodNoData = selectWoodNo.get(data);
    if (woodNoData) {
      return 'woodNoExist';
    }

    // 插入操作
    const fields = keys.join(', '); // 字段名，格式为 name, price, stock
    const placeholders = keys.map(key => `@${key}`).join(', '); // 占位符，格式为 @name, @price, @stock

    const sql = `INSERT INTO lk_wood_stock (${fields}) VALUES (${placeholders})`;

    const exec = this.demoSqliteDB.db.prepare(sql);
    const result = exec.run(data);
    if (result && result.lastInsertRowid) {
      const insertSyncCenter = `INSERT INTO lk_sync_center (ref_type, ref_id,operate_type,operate_content,create_time,create_user) 
      VALUES (@refType, @refId,@operateType,@operateContent,@createTime,@createUser)`
      const insertSyncCenterExec = this.demoSqliteDB.db.prepare(insertSyncCenter);
      data.ID = result.lastInsertRowid;
      const insertData = {
        refType: 'woodStock',
        refId: result.lastInsertRowid,
        operateType: 'add',
        operateContent: JSON.stringify(data),
        createTime: data.CREATE_TIME,
        createUser: data.CREATE_USER
      }
      const insertSyncCenterResult = insertSyncCenterExec.run(insertData);
      Log.info('=================insert lk_sync_center result:', insertSyncCenterResult);
    }
    Log.info('=================insert lk_wood_stock result:', result);
    // }
  }

  async deleteWoodStock(woodStockId) {
    // 先查询lk_wood_stock表的file_path字段，判断是否有文件，有文件则删除文件
    this.deleteWoodStockFile(woodStockId);

    const deleteSql = `DELETE FROM lk_wood_stock WHERE ID=@id`;
    const exec = this.demoSqliteDB.db.prepare(deleteSql);
    const result = exec.run({ id: woodStockId });
    Log.info('=================delete lk_wood_stock result:', result);

    const deleteSyncCenterSql = `DELETE FROM lk_sync_center WHERE ref_type=@refType AND ref_id=@refId AND operate_type=@operateType`;
    const execSyncCenter = this.demoSqliteDB.db.prepare(deleteSyncCenterSql);
    const syncCenterResult = execSyncCenter.run({ refType: 'woodStock', operateType: 'add', refId: woodStockId });
    Log.info('=================delete lk_sync_center result:', syncCenterResult);
  }

  deleteWoodStockFile(woodStockId) {
    const selectFilePathSql = `SELECT file_path FROM lk_wood_stock WHERE ID=@id`;
    const selectFilePathExec = this.demoSqliteDB.db.prepare(selectFilePathSql);
    const filePathData = selectFilePathExec.get({ id: woodStockId });
    if (filePathData && filePathData.file_path) {
      const filePath = filePathData.file_path;
      const filePathArr = filePath.split(',');
      filePathArr.forEach(file => {
        fs.unlinkSync(file);
      });
    }
  }

  async getDeptOptionsByUserId(data) {
    const selectList = this.demoSqliteDB.db.prepare(`SELECT 
			dept.id as value,
			dept.DEPT_NAME as label
			FROM labs_ck_dept dept
		    left join labs_ck_user_dept  udept on udept.dept_id=dept.id
		    where udept.user_id=@userId and dept.DEPT_TYPE=@type`);
    const list = selectList.all(data);
    return list;
  }

  async uploadWoodStockFile() {
    const { CoreApp } = EE;

    const woodId = CoreApp.request.body.woodId
    Log.info('+++++++++++++++woodId:', woodId);

    let tmpDir = "C:\\labs_great_luck_files\\" + dayjs().format('YYYYMMDD');
    // 检查目录是否存在，如果不存在则创建
    if (!fs.existsSync(tmpDir)) {
      fs.mkdirSync(tmpDir, { recursive: true }); // recursive 确保父目录不存在时也会被创建
    }
    const files = CoreApp.request.files;
    let file = files.file;

    let tmpFilePath = path.join(tmpDir, dayjs().format('YYYYMMDDHHmmss') + "_" + file.originalFilename);
    try {
      let tmpFile = fs.readFileSync(file.filepath);
      fs.writeFileSync(tmpFilePath, tmpFile);
      // 先删除已经存在的文件
      this.deleteWoodStockFile(woodId);

      // const insertSyncCenter = `INSERT INTO lk_sync_center (ref_type, ref_id,operate_type,operate_content,create_time,create_user) 
      // VALUES (@refType, @refId,@operateType,@operateContent,@createTime,@createUser)`
      // const insertSyncCenterExec = this.demoSqliteDB.db.prepare(insertSyncCenter);
      // data.ID = result.lastInsertRowid;
      // const insertData = {
      //   refType: 'woodStock',
      //   refId: woodId,
      //   operateType: 'uploadFile',
      //   operateContent: { filePath: tmpFilePath },
      //   createTime: dayjs().format('YYYY-MM-DD HH:mm:ss'),
      //   createUser: data.CoreApp.request.body.userId
      // }
      // insertSyncCenterExec.run(insertData);

      // 查询lk_sync_center表ref_type=woodStock AND ref_id=woodId AND operate_type=add的记录，取出其中的operate_content字段，转换成json对象，将filePath字段的值添加到operate_content中，再更新到lk_sync_center表中
      const selectSyncCenterSql = `SELECT operate_content FROM lk_sync_center WHERE ref_type=@refType AND ref_id=@refId AND operate_type=@operateType`;
      const execSyncCenter = this.demoSqliteDB.db.prepare(selectSyncCenterSql);
      const syncCenterData = execSyncCenter.get({ refType: 'woodStock', refId: woodId, operateType: 'add' });
      if (syncCenterData) {
        const syncCenterContent = JSON.parse(syncCenterData.operate_content);
        syncCenterContent.filePath = tmpFilePath;
        const updateSyncCenterSql = `UPDATE lk_sync_center SET operate_content=@operateContent WHERE ref_type=@refType AND ref_id=@refId AND operate_type=@operateType`;
        const execSyncCenter = this.demoSqliteDB.db.prepare(updateSyncCenterSql);
        execSyncCenter.run({ operateContent: JSON.stringify(syncCenterContent), refType: 'woodStock', refId: woodId, operateType: 'add' });
      }

      // 更新lk_wood_stock表的file_path字段
      const updateSql = `UPDATE lk_wood_stock SET FILE_PATH=@filePath WHERE ID=@id`;
      const exec = this.demoSqliteDB.db.prepare(updateSql);
      exec.run({ filePath: tmpFilePath, id: woodId });

    } finally {
      await fs.unlink(file.filepath, function () { });
    }
    // const fileStream = fs.createReadStream(tmpFilePath);
    // const uploadRes = await Services.get('framework').uploadFileToSMMS(fileStream);
    Log.info('tmpFilePath:', tmpFilePath);
    return tmpFilePath;
  }

  /**
   * 检查lk_sync_center表是否有记录
   */
  async checkSyncCenter() {
    const selectSql = `SELECT COUNT(1) as count FROM lk_sync_center`;
    const exec = this.demoSqliteDB.db.prepare(selectSql);
    const result = exec.get();
    return result.count > 0;
  }

  /**
   * 查询lk_sync_center表所有数据
   */
  async getSyncCenterList() {
    const selectSql = `SELECT * FROM lk_sync_center`;
    const exec = this.demoSqliteDB.db.prepare(selectSql);
    const result = exec.all();
    return result;
  }
  /**
   * 删除lk_sync_center表所有数据
   */
  async deleteSyncCenter() {
    const deleteSql = `DELETE FROM lk_sync_center`;
    const exec = this.demoSqliteDB.db.prepare(deleteSql);
    const result = exec.run();
    return result;
  }

  /**
   * 上传离线文件
   */
  async uploadOfflineFiles() {
    // 将C:\\labs_great_luck目录打包成zip文件，上传到服务端
    const sourceDir = 'C:\\labs_great_luck_files';
    const zipFilePath = path.join(sourceDir, 'labs_great_luck_files.zip');
    this.zipDirectory(sourceDir, zipFilePath);

    // 上传文件
    const form = new FormData();
    form.append('file', fs.createReadStream(zipFilePath));

    try {
      const response = await axios.post('http://localhost:8080/upload-offline-file', form, {
        headers: {
          ...form.getHeaders(), // 必须添加 form-data 生成的 headers
        },
      });
      Log.info('Upload successful:', response);
      // 上传成功删除 zipFilePath 文件
      await fs.unlink(zipFilePath, function () { });
      // 删除 sourceDir 目录下所有文件夹和文件
      await fs.rm(sourceDir, { recursive: true, force: true });
      return true;
    } catch (error) {
      Log.error('Upload failed:', error);
      return false;
    }

  }

  zipDirectory(sourceDir, zipFilePath) {
    const zip = new AdmZip();
    zip.addLocalFolder(sourceDir); // 添加文件夹到 zip 中
    zip.writeZip(zipFilePath); // 将 zip 保存到指定路径
    Log.info(`Directory ${sourceDir} compressed into ${zipFilePath}`);
  }

}

SqlitedbService.toString = () => '[class SqlitedbService]';
module.exports = SqlitedbService;
